package com.rybbaby.tss.front.tssStudent.dao.imp;

import com.google.common.collect.ImmutableMap;
import com.rybbaby.tss.core.utils.StringUtils;
import com.rybbaby.tss.core.web.dao.imp.GenericDao;
import com.rybbaby.tss.front.tssClasses.entity.TssClasses;
import com.rybbaby.tss.front.tssPertainRelation.entity.TssPertainRelation;
import com.rybbaby.tss.front.tssStudent.dao.ITssStudentDao;
import com.rybbaby.tss.front.tssStudent.entity.TssStudent;
import org.springframework.stereotype.Repository;

import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * @author 雷春琼
 * @description 客户表数据库操作实现
 * @createDate 2017-08-09 下午2:48:34
 */
@Repository
public class TssStudentDaoImp extends GenericDao<TssStudent> implements ITssStudentDao {
    /**
     * 支持批量修改客户状态以及名单状态
     *
     * @param immutableMap 客户状态,名单状态
     * @param ids
     * @return
     */
    @Override
    public Integer updateListStatusOrStatusByIds(ImmutableMap immutableMap, String adminUserId, String ids) {
        String sql = "";
        Set entries = immutableMap.entrySet();
        if (entries != null) {
            Iterator iterator = entries.iterator();
            while (iterator.hasNext()) {
                Map.Entry entry = (Map.Entry) iterator.next();
                Object key = entry.getKey();
                Object value = entry.getValue();
                if (StringUtils.isNotBlank(adminUserId)) {
                    sql = "update tss_student set " + key + "=" + value + " , empAdminUserId='" + adminUserId + "' , updateTime=Now() where id in('" + ids.replace(",", "','") + "')";
                } else {
                    sql = "update tss_student set " + key + "=" + value + " , empAdminUserId='" + adminUserId + "' ,updateTime=Now()  where id in('" + ids.replace(",", "','") + "')";

                }
            }
        }
        return this.updateBySql(sql);
    }

    @Override
    public List<TssStudent> findStudentByName(String name) throws Exception {
        String sql = "select * from  tss_student where name LIKE '%" + name + "%'";
        return this.findListBySql(sql, TssStudent.class, null);
    }

    @Override
    public Map<String, Object> findById(String id) {
        String sql = "select * from  tss_student where id=?";
        return this.findOneForJdbc(sql, new Object[]{id});
    }

    @Override
    public Integer updateParameterById(Map<String, Object> map, String id) {
        StringBuffer sql = new StringBuffer();
        sql.append("update tss_student set  ");
        if (map.size() > 0) {
            for (Map.Entry<String, Object> vo : map.entrySet()) {
                sql.append(vo.getKey() + "=" + "'" + vo.getValue() + "',");
            }
            sql.deleteCharAt(sql.length() - 1);
        }
        sql.append(" where id=?");
        return this.executeSql(sql.toString(), new Object[]{id});
    }

    @Override
    public List<Map<String, Object>> findTodayStudentByType(String adminUserIds) {
        String sql = "SELECT\n" +
                "\ttss_member_package.type,\n" +
                "\ttss_member_package.applyStatus,\n" +
                "\tCOUNT(tss_student.id) AS number\n" +
                "FROM\n" +
                "\ttss_student LEFT JOIN tss_member_package\n" +
                "ON tss_student.id = tss_member_package.memberId\n" +
                "WHERE\n" +
                "\tto_days(tss_member_package.createTime) = to_days(now())\n" +
                "AND tss_student.adminUserId in ('" + adminUserIds + "')" +
                "GROUP BY\n" +
                "tss_member_package.applyStatus,tss_member_package.type";
        System.out.println(sql);
        return this.findForJdbc(sql, null);
    }

    @Override
    public List<Map<String, Object>> findMonthStudentByType(String adminUserIds) {
        String sql = "SELECT\n" +
                "\ttss_member_package.type,\n" +
                "\ttss_member_package.applyStatus,\n" +
                "\tCOUNT(tss_student.id) AS number\n" +
                "FROM\n" +
                "\ttss_student LEFT JOIN tss_member_package\n" +
                "ON tss_student.id = tss_member_package.memberId\n" +
                "WHERE\n" +
                "\tDATE_FORMAT( tss_member_package.createTime, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )\n" +
                "AND tss_student.adminUserId in ('" + adminUserIds + "')" +
                "GROUP BY\n" +
                "tss_member_package.applyStatus,tss_member_package.type";
        System.out.println(sql);
        return this.findForJdbc(sql, null);
    }


    /**
     * 导出数据相关方法
     * 杨然
     */

    @Override
    public List<TssStudent> getStudentListById(List<String> stuIdList) {
        return super.loadByMultipleId(TssStudent.class, stuIdList);
    }

    @Override
    public List<TssStudent> getAllStudentList() {

        return this.loadAll(TssStudent.class);
    }

    @Override
    public List<TssStudent> findStudentByContactPhone(String contactPhone) throws Exception {
        String sql = "select * from  tss_student where contactPhone LIKE '%" + contactPhone + "%'";
        return findListBySql(sql, TssStudent.class);
    }

    @Override
    public Integer updateStudentIsMember(String id) {
        String sql = "update  tss_student set isMember=1 where id=?";
        return this.executeSql(sql, new Object[]{id});
    }

    @Override
    public List<TssStudent> findBycontractPhone(String contractPhone) {
        String sql = "from  TssStudent where contactPhone=?";
        return this.findByHql(sql, new Object[]{contractPhone});
    }

    /**
     * 验证是否有重复数据
     * 杨然
     */
    @Override
    public List<TssStudent> validationStudent(String name, String nickName, Integer sex) {
        String sql = "select a.id, a.name, a.nickName, a.sex from tss_student a where a.name in('" + name + "') and a" +
                ".nickName in('" + nickName + "') and a.sex in(" + sex + ")";
        try {
            List<TssStudent> listBySql = super.findListBySql(sql, TssStudent.class, null);
            if (!listBySql.isEmpty()) {
                return listBySql;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }


    /**
     * 导入幼儿信息返回id
     */
    @Override
    public String saveInport(TssStudent student) {

        return (String) super.save(student);
    }

    @Override
    public List<TssStudent> findByPhone(String motherPhone, String adminUserId) throws Exception {
        String sql = "SELECT\n" +
                "\t*\n" +
                "FROM\n" +
                "tss_student \n" +
                "WHERE tss_student.contactPhone = '" + motherPhone + "'\n" +
                "AND tss_student.adminUserId = '" + adminUserId + "'\n";
        System.out.println(sql);
        return super.findListBySql(sql, TssStudent.class, null);
    }

    @Override
    public List<Map<String, Object>> associatedPark(String contactPhone) {
        String sql = "SELECT\n" +
                "\tsys_school.id,\n" +
                "\tsys_school.schoolName,\n" +
                "\ttss_student.id AS studentId\n" +
                "FROM\n" +
                "\ttss_pertain_relation\n" +
                "LEFT JOIN tss_student ON tss_pertain_relation.studentId = tss_student.id\n" +
                "LEFT JOIN sys_admin_user ON sys_admin_user.id = tss_student.adminUserId\n" +
                "LEFT JOIN sys_school ON sys_admin_user.schoolId = sys_school.id\n" +
                "WHERE tss_pertain_relation.contactPhone = ?";
        System.out.println(sql);
        return super.findForJdbc(sql, new Object[]{contactPhone});
    }

    @Override
    public List<TssStudent> findByQuickMark(String quickMark) throws Exception {
        String sql = "SELECT\n" +
                "\t*\n" +
                "FROM\n" +
                "\ttss_student\n" +
                "WHERE tss_student.quickMark = '" + quickMark + "'";
        return findListBySql(sql, TssStudent.class);
    }

    @Override
    public List<TssStudent> getStudentsByClassId(String classId) {
        String sql = "SELECT *  FROM tss_student WHERE id IN (SELECT memberId count FROM tss_member_attend_class_record WHERE lessonsId in(select id from tss_class_lessons WHERE classId=?) GROUP BY memberId)";
        return this.findListBySql(sql, TssStudent.class, classId);
    }
}
